
if exists(select * from sys.objects where name='UP_Proc_CategoryListSite' and type='P')
drop proc UP_Proc_CategoryListSite
go

create proc UP_Proc_CategoryListSite --UP_Proc_CategoryListSite 322,1
(
	@cid int,
	@LanguageID int
)
as
	;with temp
	as
	(
		select top 1 CategoryID,ParentID,CategoryName,1 rowCnt from PCategory where CategoryID=@cid and LanguageID=@LanguageID 
		union all
		select a.CategoryID,a.ParentID,a.CategoryName,(rowCnt+1) rowCnt from PCategory a inner join temp on a.CategoryID=temp.ParentID 
		where a.LanguageID=@LanguageID
	) select * from temp order by rowCnt desc
	
	
